package Dao;
import Model1.goods;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.cell.PropertyValueFactory;

import java.sql.*;
import java.util.*;
public class goodsDao {
	
	private static PreparedStatement ps=null;
	private static ResultSet rs=null;
	private static Connection conn=null;
	
	public static Boolean Insert(goods good) {
		if(good==null)
			return false;
		conn = DatabaseConn.getConn();
		String sql = "insert into goods(name,id,sellingprice,costprice,inventory,kind) values(?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, good.getName());
			ps.setInt(2, good.getId());
			ps.setFloat(3, good.getSellingPrice());
			ps.setFloat(4, good.getCostPrice());
			ps.setInt(5, good.getInventory());
			ps.setInt(6, good.getKind());
			int i = ps.executeUpdate();
			
			DatabaseConn.close(conn, ps, rs);
			return i==1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public static List<goods> SearchByKind(int kind) {
		if(kind<=0)
			return null;
		conn = DatabaseConn.getConn();
		List<goods> good=new ArrayList<>();
		String sql = "select * from goods where kind like "+kind;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				good.add(new goods(rs.getString("name"),rs.getInt("id"),rs.getFloat("sellingprice"),rs.getFloat("costprice"),rs.getInt("inventory"),rs.getInt("kind")));
			}
			DatabaseConn.close(conn, ps, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return good;
	}
	
	public static goods SearchByName(String name) {
		if(name.length()<=0)
			return null;
		conn = DatabaseConn.getConn();
		goods good = null;
		String sql = "select * from goods where name like '"+name+"'";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				good = new goods(rs.getString("name"),rs.getInt("id"),rs.getFloat("sellingprice"),rs.getFloat("costprice"),rs.getInt("inventory"),rs.getInt("kind"));
			}
			DatabaseConn.close(conn, ps, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return good;
	}
	
	public static goods SearchById(int id) {
		if(id<=0)
			return null;
		conn = DatabaseConn.getConn();
		goods good = null;
		String sql = "select * from goods where id like "+id;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				good = new goods(rs.getString("name"),rs.getInt("id"),rs.getFloat("sellingprice"),rs.getFloat("costprice"),rs.getInt("inventory"),rs.getInt("kind"));
			}
			DatabaseConn.close(conn, ps, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return good;
	}
	
	public static Boolean Delete(int id) {
		if(id<=0)
			return false;
		conn = DatabaseConn.getConn();
		String sql = "delete from goods where id like "+id;
		try {
			ps = conn.prepareStatement(sql);
			int i =ps.executeUpdate();
			DatabaseConn.close(conn, ps, rs);
			return i==1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public static Boolean Update(goods good) {
		conn = DatabaseConn.getConn();
		String sql = "update goods set name=?,sellingprice=?,costprice=?,inventory=?,kind=? where id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, good.getName());
			ps.setFloat(2, good.getSellingPrice());
			ps.setFloat(3, good.getCostPrice());
			ps.setInt(4, good.getInventory());
			ps.setInt(5, good.getKind());
			ps.setInt(6, good.getId());
			int i = ps.executeUpdate();
			DatabaseConn.close(conn, ps, rs);
			return i==1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public static Boolean UpdateGoods(int sales,int id) {
		conn = DatabaseConn.getConn();
		String sql = "update goods set inventory=inventory-? where id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,sales);
			ps.setInt(2,id);
			
			int i = ps.executeUpdate();
			DatabaseConn.close(conn, ps, rs);
			return i==1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
	
	public static List<goods> findAll() {
		conn = DatabaseConn.getConn();
		List<goods> good=new ArrayList<>();
		String sql = "select * from goods";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				good.add(new goods(rs.getString("name"),rs.getInt("id"),rs.getFloat("sellingprice"),rs.getFloat("costprice"),rs.getInt("inventory"),rs.getInt("kind")));
			}
			DatabaseConn.close(conn, ps, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return good;
	}
	
	public static List<goods> findHave() {//search for goods that inventory is > 0
		conn = DatabaseConn.getConn();
		List<goods> good=new ArrayList<>();
		String sql = "select * from goods where inventory > 0";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				good.add(new goods(rs.getString("name"),rs.getInt("id"),rs.getFloat("sellingprice"),rs.getFloat("costprice"),rs.getInt("inventory"),rs.getInt("kind")));
			}
			DatabaseConn.close(conn, ps, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return good;
	}
	
	
	
}
